﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Basic4Net.Utility.Excel
{
	public class ReadExcel
	{
	    private String _FileFullName;
        private String FileFullName
        {
            get { return _FileFullName; }
		    set { _FileFullName = value; }
        }
		private HDR? hdr;
		private OleDbConnection conn;

		#region 构造函数
		public ReadExcel(String FileFullName,HDR hdr)
		{
			this.FileFullName = FileFullName;
			this.hdr = hdr;
		}

		public ReadExcel(String FileFullName)
			: this(FileFullName, HDR.Yes)
		{ }
		#endregion

		#region 私有方法
		/// <summary>
		/// 初始化conn
		/// </summary>
		private void InitConnection()
		{
			//源的定义
			ExcelConnectionString ecs = new ExcelConnectionString(this.FileFullName, this.hdr);
			string strConn = ecs.ConnectionString();

			//连接数据源
			this.conn = new OleDbConnection(strConn);
		}

		/// <summary>
		/// 去除datatable中的空行
		/// </summary>
		/// <param name="dt"></param>
		private void ClearNullRows(DataTable dt)
		{
			for (int i = 0; i < dt.Rows.Count; i++)
			{
				if (DBNull.Value.Equals(dt.Rows[i][0]) || dt.Rows[i][0].ToString() == String.Empty)
				{
					bool Flag = false;
					for (int j = 1; j < dt.Columns.Count; j++)
					{
						if (!DBNull.Value.Equals(dt.Rows[i][j]) && dt.Rows[i][j].ToString() != String.Empty)
						{
							Flag = true;
							break;
						}
					}
					if (!Flag)
					{
						dt.Rows.RemoveAt(i);
						i--;
					}
				}
			}
		}

		/// <summary>
		/// 去除datatable中的空列
		/// </summary>
		/// <param name="dt"></param>
		private void ClearNullColumns(DataTable dt)
		{
			for (int i = 0; i < dt.Columns.Count; i++)
			{
				if (DBNull.Value.Equals(dt.Rows[0][i]) || dt.Rows[0][i].ToString() == String.Empty)
				{
					bool Flag = false;
					for (int j = 1; j < dt.Rows.Count; j++)
					{
						if (!DBNull.Value.Equals(dt.Rows[j][i]) && dt.Rows[j][i].ToString() != String.Empty)
						{
							Flag = true;
							break;
						}
					}
					if (!Flag)
					{
						dt.Columns.RemoveAt(i);
						i--;
					}
				}
			}
		}

		#endregion

		#region 公用方法
		/// <summary>
		/// 获取Excel文件内Sheet名的集合。
		/// </summary>
		/// <returns></returns>
		public List<String> TableNames()
		{
			if (this.conn == null)
				InitConnection();
			List<String> TableNames = new List<string>();
			try
			{
				if (!System.IO.File.Exists(this.FileFullName))
				{
					throw new Exception(String.Format("文件【{0}】不存在。", this.FileFullName));
				}

				this.conn.Open();//打开连接
				DataTable dtSheetName = this.conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
				string temp;
				for (int i = 0; i < dtSheetName.Rows.Count; i++)
				{
					temp = dtSheetName.Rows[i]["TABLE_NAME"].ToString();
					if (temp[temp.Length - 1] == '\'' && temp.Length >= 3)
						temp = temp.Substring(1, temp.Length - 3);
					else
						temp = temp.Substring(0, temp.Length - 1);
					TableNames.Add(temp);
				}
				this.conn.Close();//关闭连接
			}
			catch (Exception ex)
			{
				if (ex.Message.LastIndexOf("它已经被别的用户以独占方式打开，或没有查看数据的权限。") != -1)
				{
					throw new Exception(String.Format("文件【{0}】\r\n被别的用户以独占方式打开，或没有查看数据的权限。",this.FileFullName));
				}
				else
				{
					throw ex;
				}	
			}
			return TableNames;
		}

		/// <summary>
		/// 获取某个Sheet的DataTable数据。
		/// </summary>
		/// <param name="SheetName"></param>
		/// <returns></returns>
		public DataTable Sheet2DataTable(string SheetName)
		{
			DataSet ds = new DataSet();
			if (TableNames().Contains(SheetName))
			{
				conn.Open();	//打开连接
				string ExcelSql = string.Format("select * from [{0}$]", SheetName);//"select * from   [sheet1$]"
				OleDbDataAdapter adapter = new OleDbDataAdapter(ExcelSql, conn.ConnectionString);
				adapter.Fill(ds, SheetName);
				conn.Close();	//关闭连接
			}
			//this.ClearNullColumns(ds.Tables[SheetName]);
			this.ClearNullRows(ds.Tables[SheetName]);
			return ds.Tables[SheetName];
		}
		#endregion
	}
}
